package com.bookm.service.excel1;

import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.model.SharedStringsTable;
import org.xml.sax.Attributes;
import org.xml.sax.InputSource;
import org.xml.sax.XMLReader;
import org.xml.sax.helpers.DefaultHandler;
import org.xml.sax.helpers.XMLReaderFactory;

import java.io.File;
import java.io.FileInputStream;

/**
 * 普通的实验测试
 * 能实现对数字的识别
 */
public class ExcelStreamReader {
    public static void main(String[] args) throws Exception {
        File file = new File("data.xlsx");
        FileInputStream fileInputStream = new FileInputStream(file);
        processLargeExcel(file);
    }

    static public void processLargeExcel(File is) throws Exception {
        try (OPCPackage pkg = OPCPackage.open(is)) {
            XSSFReader reader = new XSSFReader(pkg);
            SharedStringsTable sst = reader.getSharedStringsTable();
            XMLReader parser = XMLReaderFactory.createXMLReader();

            // 自定义内容处理器
            SheetHandler handler = new SheetHandler(sst);
            parser.setContentHandler(handler);

            // 只处理第一个 Sheet
            InputSource sheetSource = new InputSource(reader.getSheetsData().next());
            parser.parse(sheetSource);
        }
    }

    // 自定义 Sheet 内容处理器（精简示例）
    static private class SheetHandler extends DefaultHandler {
        private SharedStringsTable sst;
        private StringBuilder cellValue = new StringBuilder();
        private boolean isCellValue;

        public SheetHandler(SharedStringsTable sst) {
            this.sst = sst;
        }

        @Override
        public void startElement(String uri, String localName, String qName, Attributes attributes) {
            if (qName.equals("c")) {
                // 获取单元格类型（s=字符串，n=数字）
                String cellType = attributes.getValue("t");
                if ("s".equals(cellType)) {
                    isCellValue = true;
                }
            } else if (qName.equals("v")) {
                isCellValue = true;
            }
        }

        @Override
        public void endElement(String uri, String localName, String qName) {
            if (qName.equals("row")) {
                // 处理完一行数据（在此处实现你的业务逻辑）
                System.out.println("Row processed");
            } else if (qName.equals("v")) {
                // 解析单元格值
                if (isCellValue) {
                    String value = cellValue.toString();
                    // 如果是共享字符串类型，从 SST 中获取实际值
                    if (sst != null) {
                        int idx = Integer.parseInt(value);
                        value = sst.getItemAt(idx).getString();
                    }
                    System.out.println("Cell Value: " + value);
                    cellValue.setLength(0);
                    isCellValue = false;
                }
            }
        }

        @Override
        public void characters(char[] ch, int start, int length) {
            if (isCellValue) {
                cellValue.append(ch, start, length);
            }
        }
    }
}